﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace UbbTimetable2
{
    /// <summary>
    /// A struct to store classes data.
    /// </summary>
    public struct Classes
    {
        int _id;

        public int Id
        {
            get { return _id; }
            set { _id = value; }
        }
        int _typeId;

        public int TypeId
        {
            get { return _typeId; }
            set { _typeId = value; }
        }
        String _typeName;

        public String TypeName
        {
            get { return _typeName; }
            set { _typeName = value; }
        }
        int _classNameId;

        public int ClassNameId
        {
            get { return _classNameId; }
            set { _classNameId = value; }
        }
        String _className;

        public String ClassName
        {
            get { return _className; }
            set { _className = value; }
        }
        int _profId;

        public int ProfId
        {
            get { return _profId; }
            set { _profId = value; }
        }
        String _profFullName;

        public String ProfFullName
        {
            get { return _profFullName; }
            set { _profFullName = value; }
        }

    }

    /// <summary>
    /// A struct to store class type data.
    /// </summary>
    public struct ClassType
    {
        int _typeId;

        public int TypeId
        {
            get { return _typeId; }
            set { _typeId = value; }
        }
        String _typeName;

        public String TypeName
        {
            get { return _typeName; }
            set { _typeName = value; }
        }
    }

    /// <summary>
    /// A struct to store class name data.
    /// </summary>
    public struct ClassNames
    {
        int _classId;

        public int ClassId
        {
          get { return _classId; }
          set { _classId = value; }
        }

        String _className;

        public String ClassName
        {
          get { return _className; }
          set { _className = value; }
        }
    }

    /// <summary>
    /// A DAO that handles all data related to classes.
    /// </summary>
    public class ClassesDao : Dao
    {
        private String err = "";
        /// <summary>
        /// The last error message.
        /// </summary>
        public String Err
        {
            get { return err; }
        }

        /// <summary>
        /// Used to list all classes.
        /// </summary>
        public const int ALL = 0;
        /// <summary>
        /// Used to list only unscheduled classes.
        /// </summary>
        public const int UNSCHEDULED = 1;

        /// <summary>
        /// Inserts a new class into the database.
        /// </summary>
        /// <param name="tanarId">The id of the professor.</param>
        /// <param name="oraNevId">The id of the class.</param>
        /// <param name="oraTipusId">The id of the class type.</param>
        public void InsertClass(int tanarId, int oraNevId, int oraTipusId)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("INSERT INTO Ora (TanarID, OraNevID, OraTipusID) VALUES (");
            sb.Append(tanarId);
            sb.Append(",");
            sb.Append(oraNevId);
            sb.Append(",");
            sb.Append(oraTipusId);
            sb.Append(")");

            err = ExecuteNonQuery(sb.ToString());
        }

        /// <summary>
        /// Updates a class in the database.
        /// </summary>
        /// <param name="oraId">The id of the class</param>
        /// <param name="tanarId">The id of the professor.</param>
        /// <param name="oraNevId">The id of the class.</param>
        /// <param name="oraTipusId">The id of the class type.</param>
        public void UpdateClass(int oraId, int tanarId, int oraNevId, int oraTipusId)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("UPDATE Ora SET TanarID=");
            sb.Append(tanarId);
            sb.Append(", OraNevID=");
            sb.Append(oraNevId);
            sb.Append(", OraTipusID=");
            sb.Append(oraTipusId);
            sb.Append(" WHERE ID=");
            sb.Append(oraId);

            err = ExecuteNonQuery(sb.ToString());
        }

        /// <summary>
        /// Updates a class name.
        /// </summary>
        /// <param name="cnId">The id of the class name.</param>
        /// <param name="clName">The new name of the class.</param>
        public void UpdateClassName(int cnId, String clName)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("UPDATE OraNevek SET Nev='");
            sb.Append(clName);
            sb.Append("' WHERE ID=");
            sb.Append(cnId);

            err = ExecuteNonQuery(sb.ToString());
        }

        /// <summary>
        /// Inserts a new class.
        /// </summary>
        /// <param name="clName">The name of the class.</param>
        public void InsertClassName(String clName)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("INSERT INTO OraNevek (Nev) VALUES ('");
            sb.Append(clName);
            sb.Append("')");

            err = ExecuteNonQuery(sb.ToString());
        }

        /// <summary>
        /// Deletes a class.
        /// </summary>
        /// <param name="clId">The id of the class.</param>
        public void DeleteClassName(int clId)
        {
            String command = "DELETE FROM OraNevek WHERE ID = " + clId.ToString();

            err = ExecuteNonQuery(command);
        }

        /// <summary>
        /// Deletes a class from the database.
        /// </summary>
        /// <param name="oraId">The id of the class.</param>
        public void DeleteClass(int oraId)
        {
            String query = "DELETE FROM Ora WHERE ID=" + oraId;

            err = ExecuteNonQuery(query);
        }

        /// <summary>
        /// Returns the list of classes.
        /// </summary>
        /// <returns>Returns the list of all the classes.</returns>
        public List<Classes> GetClassesList()
        {
            return GetClassesList(ALL);
        }

        /// <summary>
        /// Returns the list of certain classes.
        /// </summary>
        /// <param name="rule">The rule that will be used. <see cref="ALL"/> <see cref="UNSCHEDULED"/></param>
        /// <returns>The list of classes according to the rule.</returns>
        public List<Classes> GetClassesList(int rule)
        {
            String query;
            switch (rule)
            {
                case UNSCHEDULED:
                    query = "SELECT * FROM Orak WHERE ID NOT IN ( SELECT OraID FROM Tanitas )";
                    break;
                default:
                    query = "SELECT * FROM Orak";
                    break;
            }

            SqlDataReader rdr = ExecuteReader(query, ref err);

            List<Classes> list = new List<Classes>();
            if (err == "OK")
            {
                while (rdr.Read())
                {
                    Classes item = new Classes();
                    item.Id = Convert.ToInt32(rdr[0]);
                    item.TypeId = Convert.ToInt32(rdr[1]);
                    item.TypeName = rdr[2].ToString().TrimEnd();
                    item.ClassNameId = Convert.ToInt32(rdr[3]);
                    item.ClassName = rdr[4].ToString().TrimEnd();
                    item.ProfId = Convert.ToInt32(rdr[5]);
                    item.ProfFullName = rdr[6].ToString().TrimEnd() +
                        " " + rdr[7].ToString().TrimEnd() + " " +
                        rdr[8].ToString().TrimEnd();

                    list.Add(item);
                }
            }
            CloseDataReader(rdr);
            return list;
        }

        /// <summary>
        /// Returns the list of class types.
        /// </summary>
        /// <returns>The list of class types.</returns>
        public List<ClassType> GetTypes()
        {
            String query = "SELECT ID, Nev FROM OraTipus";

            SqlDataReader rdr = ExecuteReader(query, ref err);

            List<ClassType> list = new List<ClassType>();
            if (err == "OK")
            {
                while (rdr.Read())
                {
                    ClassType item = new ClassType();
                    item.TypeId = Convert.ToInt32(rdr[0]);
                    item.TypeName = rdr[1].ToString().TrimEnd();

                    list.Add(item);
                }
            }
            CloseDataReader(rdr);
            return list;
        }

        /// <summary>
        /// Returns the list of class names.
        /// </summary>
        /// <returns>The list of class names.</returns>
        public List<ClassNames> GetClassNames()
        {
            String query = "SELECT ID, Nev FROM OraNevek";

            SqlDataReader rdr = ExecuteReader(query, ref err);

            List<ClassNames> list = new List<ClassNames>();
            if (err == "OK")
            {
                while (rdr.Read())
                {
                    ClassNames item = new ClassNames();
                    item.ClassId = Convert.ToInt32(rdr[0]);
                    item.ClassName = rdr[1].ToString().TrimEnd();

                    list.Add(item);
                }
            }
            CloseDataReader(rdr);
            return list;
        }
    }
}
